package gov.nhso.prov.acc.apps.acc6;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;

import gov.nhso.prov.acc.beans.Acc6IncomeBean;
import gov.nhso.prov.acc.beans.Acc6TransactionBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings("rawtypes")
public class ACC60205 extends Utility{
	public String url = "/apps/acc6/ACC60205A.jsp";

	public ACC60205(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if (job.equals("init")) {
			this.init(request);
		} else if(job.equals("search")){
			this.setLastRequest(request, "lastSearch");
			this.search(request);
		} else if (job.equals("view")) {
			this.view(request);
		} else if (job.equals("cancel")) {
			this.cancel(request);
		} else if (job.equals("back")){
			if (this.getLastRequest(request, "lastSearch")) {
				this.search(request);
			} else {
				this.init(request);
			}
		}
	}
	private String init(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			PageBean pageBean = new PageBean();
			Acc6IncomeBean incomeBean = new Acc6IncomeBean();
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String incomeType = this.getAttr(request, "incomeType");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String condition = "";
			
			if(!dao.chkData(startDate).equals("")){
				startDate = this.dateUtil().changeFormatDate(startDate, "/", "/");
				condition += " AND TRUNC(INCOME_DATE) >= TO_DATE('"+startDate+"','DD/MM/YYYY') ";
			}			
			if(!dao.chkData(endDate).equals("")){
				endDate = this.dateUtil().changeFormatDate(endDate, "/", "/");
				condition += " AND TRUNC(INCOME_DATE) <= TO_DATE('"+endDate+"','DD/MM/YYYY') ";
			}
			incomeBean.setProvinceId(provinceId);
			incomeBean.setBudgetYear(budgetYear);
			incomeBean.setActive("Y");
			incomeBean.setIncomeType(incomeType);
			incomeBean.setIncomeStatus("Y");

			this.beanUtil().copyRequest2Bean(request, pageBean);
			if (this.user(request).getAreaLevel().equals("2")){	
				condition = " AND PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '" + this.user(request).getAreaCode()+ "' ) ";
			}
			pageBean = dao.selectPage(incomeBean,condition, pageBean);
			
			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}

			request.setAttribute("pageBean", pageBean);
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("provinceId", incomeBean.getProvinceId());
			request.setAttribute("budgetYear", incomeBean.getBudgetYear());
			request.setAttribute("incomeType", incomeBean.getIncomeType());
			request.setAttribute("transactionTimestamp", "");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String view(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String refNo = this.getAttr(request, "refNo");
			String allocateCode = "";

			Map<String, String> xcols = new HashMap<String, String>();
			xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
			
			Acc6IncomeBean aib = new Acc6IncomeBean();
			aib.setDocNo(docNo);
			aib.setProvinceId(provinceId);
			aib.setBudgetYear(budgetYear);
			aib.setRefNo(refNo);
			dao.select(aib, xcols , "");
			allocateCode = aib.getAllocateCode();
			request.setAttribute("incomeBean", aib);
			request.setAttribute("allocateDisplay", allocateCode + " - " + this.dataUtil(dao).getAllocateById(allocateCode));
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60205B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String cancel(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		Acc6IncomeBean incomeBean = new Acc6IncomeBean();
		Acc6TransactionBean atb = new Acc6TransactionBean();
		StringBuffer sb = new StringBuffer();
		Map map = new HashMap();
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			this.beanUtil().copyRequest2Bean(request, incomeBean);
			String transactionTimestamp = incomeBean.getTransactionTimestamp();
			String userId = this.user(request).getUserId();
			incomeBean.setIncomeStatus("P");
			incomeBean.setTransactionTimestamp("null");
			incomeBean.setUpdatedBy(userId);
			incomeBean.setUpdatedDate(this.sysDate);
			dao.update(incomeBean);
			atb.setTransactionTimestamp("TO_TIMESTAMP('" + transactionTimestamp + "', 'YYYYMMDDHH24MISSFF3')");
			dao.delete(atb);
			
			sb.append("SELECT IS_RETURN ");
			sb.append("FROM " + this.schema + ".ACC6_TRANSACTION ");
			sb.append("WHERE REF_NO = '"+ incomeBean.getRefNo() +"' AND IS_BORROW = 'Y' AND REASON_CODE = '00' ");
			sb.append("AND TRANSACTION_TYPE IN ('T','C') AND PROVINCE_ID = '" + incomeBean.getProvinceId() + "' AND BUDGET_YEAR = '" + incomeBean.getBudgetYear() + "' ");
			map = dao.findDataSql(sb.toString());
			String isReturn = dao.chkData(map.get("IS_RETURN"));
			if (isReturn.equals("Y")) {
				sb.setLength(0);
				sb.append("UPDATE " + this.schema + ".ACC6_TRANSACTION SET IS_RETURN = 'N' ");
				sb.append("WHERE REF_NO = '"+ incomeBean.getRefNo() +"' AND IS_BORROW = 'Y' AND REASON_CODE = '00' ");
				sb.append("AND TRANSACTION_TYPE IN ('T','C') AND PROVINCE_ID = '" + incomeBean.getProvinceId() + "' AND BUDGET_YEAR = '" + incomeBean.getBudgetYear() + "'");
				dao.execDataSql(sb.toString());
			}
			
			this.message(request).setMsg("I", "บันทึกข้อมูลสำเร็จ");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				request.setAttribute("docNo", "");
				request.setAttribute("budgetYear", incomeBean.getBudgetYear());
				request.setAttribute("incomeType", incomeBean.getIncomeType());
				request.setAttribute("transactionTimestamp", "");
				request.setAttribute("provinceId", incomeBean.getProvinceId());
				this.search(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
}
